﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace QLKS.Models
{
    static public class NguoiDungModel
    {
        static public DataTable GetAllNguoiDung()
        {
            return DataProviderModel.QueryResults(string.Format("select * from Users")).Tables[0];
        }

        //Lấy tất cả người dùng
        static public DataTable GetAllNguoiDung(string selectColumns)
        {
            return DataProviderModel.QueryResults(string.Format(@"SELECT {0} from v_dmNguoiDung", selectColumns.Trim() == "" ? "*" : selectColumns)).Tables[0];
        }
        
        //Kiểm tra người dùng tồn tại
        static public bool CheckUser(string username)
        {
            return ((int)DataProviderModel.ScalarExecute("SELECT COUNT(*) from Users WHERE username = '" + username + "'")) > 0;
        }

        //Tạo mới Người dùng
        static public DataTable CreateNewNguoiDung()
        {
            DataTable tb = DataProviderModel.QueryResults("SELECT * FROM Users WHERE 1=0").Tables[0];
            DataRow row = tb.NewRow();
            row["username"] = "";
            row["password"] = "";
            row["nhom"] = "2";
            row["manhanvien"] = "";
            row["trangthai"] = 1;
            tb.Rows.Add(row);
            return tb;
        }

        //Lấy Nhóm người dùng
        static public DataTable GetNhomNguoiDung()
        {
            return DataProviderModel.QueryResults("select * from NhomUser").Tables[0];
        }

        //Lấy mã nhân vien
        static public string GetMaNhanVien(string username)
        {
            object manv = DataProviderModel.ScalarExecute("SELECT manhanvien FROM v_dmNguoiDung WHERE username = '" + username + "'");
            if (manv == null)
                return "";
            return manv.ToString();
        }
        
        //Create QueryUpdate
        static public string CreateQueryUpdate(string columns)
        {
            string[] listColumns = columns.Split(',');
            string result = string.Empty;
            foreach (string cl in listColumns)
                result += cl + "= @" + cl + ",";
            if (result.Length > 0)
                result = result.Substring(0, result.Length - 1);
            return result;
        }

        //Thêm người dùng
        static public int ThemNguoiDung(DataTable tb)
        {
            string username = tb.Rows[0]["username"].ToString();
            if (GetAllNguoiDung("username").Select("username ='" + username + "'").Length <= 0)
                return DataProviderModel.Insert(tb, "Users", "");
            else
                return DataProviderModel.Update(tb, "Users", "username");
        }

        //Kích hoạt user
        static public void KichHoatNguoiDung(string username)
        {
            string query = @"UPDATE Users SET trangthai = 1 WHERE username='" + username + "'";
            DataProviderModel.RunExecute(query);
        }

        //Khóa user
        static public void KhoaNguoiDung(string username)
        {
            string query = @"UPDATE Users SET trangthai = 0 WHERE username='" + username + "'";
            DataProviderModel.RunExecute(query);
        }

        //Xóa người dùng
        static public int XoaNguoiDung(string username)
        {
            DataTable tb = new DataTable();
            tb.Columns.Add("username");
            tb.Rows.Add(username);
            return DataProviderModel.Detele(tb, "Users");
        }
    }
}
